package com.trace.demo.repository;

import com.trace.demo.entity.Order;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
import java.util.Optional;

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {


    Order findByProductId(Long id);

    List<Order> findByUserId(Long userId);

    List<Order> findByUserIdOrderByOrderDateDesc(Long userId);

    Page<Order> findByUserId(Long userId, Pageable pageable);

    Optional<Order> findByOrderNumber(String orderNumber);

    Optional<Order> findByOrderNo(String orderNo);

    @Query("SELECT COUNT(o) FROM Order o")
    Long countOrders();

    @Query("SELECT COUNT(o) FROM Order o WHERE DATE(o.orderDate) = CURRENT_DATE")
    Long countTodayNewOrders(LocalDateTime startOfDay);

    @Query("SELECT COUNT(o) FROM Order o WHERE o.status = :status")
    Long countOrdersByStatus(@Param("status") Order.OrderStatus status);

    Long countByStatus(Order.OrderStatus status);

    List<Order> findByOrderDateBetween(LocalDateTime startDate, LocalDateTime endDate);

    List<Order> findByStatusAndDealerId(Order.OrderStatus status, Long dealerId);

    List<Order> findTop5ByOrderByOrderDateDesc();

    @Query("SELECT o.status as status, COUNT(o) as count FROM Order o GROUP BY o.status")
    List<Map<String, Object>> countOrdersByStatus();

    @Query("SELECT FUNCTION('DATE_FORMAT', o.orderDate, '%Y-%m-%d') as date, COUNT(o) as count FROM Order o WHERE o.orderDate >= ?1 GROUP BY date ORDER BY date ASC")
    List<Map<String, Object>> countOrdersLast7Days(LocalDateTime startDate);
}
